IF OBJECT_ID('dbo.RptAgreementTermination') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAgreementTermination
    IF OBJECT_ID('dbo.RptAgreementTermination') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAgreementTermination >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAgreementTermination >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAgreementTermination
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 6/21/2007
--
-- Description: Source of Report, "AgreementTerminationReport.rdl"
--              In Insight DB Reporting Services
--
-- Parameters:  
--               @StartDate       
--               @EndDate         
--               @Institution               
--               @PostAwardManager     
--               @Department                
--               @domainUserId         
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAgreementTermination( )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/21/07     KC        Created
-- 7/9/07      KC        Changed Naming Conventions. Finalized Rpt.
-- 7/18/2007   KC        Added Unit and AgreementType
-- 7/23/07     KC        Fact table now hAS PropNumber on it.
-- 8/1/07      KC        Added @Department AS parameter, BudgetDate fix.
-- 8/6/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
--                       Removed WalkerNbr, Added Institution as Param
--                       and Return field for Grouping
-- 8/24/07     KC        Finalized for QA. Fixed ProjectDates
-- 8/27/07     KC        Changes ProjectEnd &Start Dates to Agreement table
-- 8/28/07     KC        Removed SvcEntityId from JOIN on Agreement Table.
--                       changed WHERE clause to use AG.ProjectEndDate
-- 9/10/07     KC        Changed to new source tables
-- 9/19/07     KC        Added @PreAwardManager. Fixed SponsorAgreementID
-- 9/25/07     KC        Used ID's for Inst,Dept
-- 9/26/07     KC        Added @Unit
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptAgreementTermination
     (
          @StartDate                  DATETIME,      --Project End Date
          @EndDate                    DATETIME,
          @Institution                VARCHAR(100) = NULL,
          @Department                 VARCHAR(100) = NULL,
          @Unit                       VARCHAR(100) = NULL,
          @PreAwardManager            VARCHAR(100) = NULL,
          @PostAwardManager           VARCHAR(100) = NULL, 
          @DomainUserId               INT
     )
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error                   INT,
               @RowCount                INT
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     IF @PreAwardManager IN ('ALL','','0') SET @PreAwardManager = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Return Query Results
     SELECT    AI.Department AS Department,
               AI.Institution AS Institution,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS Unit,
               AI.FolderNumber AS ProposalNumber,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.SponsorName AS Sponsor,
               AI.SponsorAgreementID AS SponsorAgreementID,
               AI.SponsorTypeName AS SponsorType,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               AI.ActivityDescription AS ActivityType,
               AI.ProposalTitle AS Title,
               AI.AgreementType AS AgreementType,
               AI.PostAwardManagerCode AS PostAwardManager,
               ISNULL(AI.PostAwardManagerName,'Post Award Manager Not Assigned') AS PostAwardManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Pre Award Manager Not Assigned') AS PreAwardManagerName,
               AI.ResourceId AS AgrResourceId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN

               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId

                         
     WHERE     (AI.ProjectEndDate BETWEEN  @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@PostAwardManager IS NULL OR AI.PostAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PostAwardManager ))) 
     ORDER BY AI.PrincipalInvestigator

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptAgreementTermination') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAgreementTermination >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAgreementTermination >>>'
go

GRANT EXECUTE ON dbo.RptAgreementTermination TO InsightReport,Insight
go
